﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace MetroTemplate.Model
{
    public class FacturesDataService
    {
        private const string ConnectionString = "data source=BILOG-PRT-012\\MSSQLSERVER2012;initial catalog=BudgetDb;user id=sa;password=123;";

        public void GetData(Action<List<FacturesModel>, Exception> callback)
        {
            try
            {

                List<FacturesModel> result = new List<FacturesModel>();
                SqlConnection conn = new SqlConnection(ConnectionString);
                SqlCommand comm = conn.CreateCommand();
                conn.Open();
                comm.CommandText = @"SELECT [FactureId]
                                      ,[Libelle]
                                      ,[Title]
                                      ,[Telephone]
                                      ,[DateFacture]
                                      ,[Imprime]
                                      ,[Informations]
                                      ,[Remarques] FROM [dbo].[Factures]";
                SqlDataReader reader = comm.ExecuteReader();
                while(reader.Read())
                {
                    FacturesModel fm = new FacturesModel();

                    if ( !reader.IsDBNull(4) )
                        fm.DateFacture = reader.GetDateTime(4);

                    if ( !reader.IsDBNull(0) )
                        fm.FactureId = reader.GetInt64(0);

                    if ( !reader.IsDBNull(6) )
                        fm.Informations = reader.GetString(6);

                    if ( !reader.IsDBNull(1) )
                        fm.Libelle = reader.GetString(1);

                    if ( !reader.IsDBNull(7) )
                        fm.Remarques = reader.GetString(7);

                    if ( !reader.IsDBNull(3) )
                        fm.Telephone = reader.GetString(3);

                    if ( !reader.IsDBNull(2) )
                        fm.Title = reader.GetString(2);

                    // On charge la liste des article pour cette facture
                    SqlConnection articleConn = new SqlConnection(ConnectionString);
                    articleConn.Open();
                    SqlCommand ArticleComm = articleConn.CreateCommand();
                    ArticleComm.CommandText = @"SELECT [ArticleId]
                                                  ,[Libelle]
                                                  ,[Quatite]
                                                  ,[PrixUnitaire]
                                              FROM [dbo].[Articles] WHERE [FactureId] = " + fm.FactureId;
                    SqlDataReader ArticleReader = ArticleComm.ExecuteReader();

                    while (ArticleReader.Read())
                    {
                        ArticleModel article = new ArticleModel();

                        if (!ArticleReader.IsDBNull(0))
                            article.ArticleId = ArticleReader.GetInt64(0);

                        if (!ArticleReader.IsDBNull(1))
                            article.Libelle = ArticleReader.GetString(1);

                        if (!ArticleReader.IsDBNull(2))
                            article.Quatite = ArticleReader.GetDouble(2);

                        if (!ArticleReader.IsDBNull(3))
                            article.PrixUnitaire = ArticleReader.GetDouble(3);

                        fm.Articles.Add(article);
                    }

                    ArticleReader.Close();
                    ArticleReader.Dispose();
                    ArticleComm.Dispose();
                    articleConn.Close();
                    articleConn.Close();
                    articleConn.Dispose();
                    result.Add(fm);
                }

                reader.Close();
                reader.Dispose();
                comm.Dispose();
                conn.Close();

                callback(result, null);
            }
            catch(Exception ex)
            {
                callback(null, ex);
            }
        }

        public void UpdateItem(FacturesModel model,string propertyName)
        {
            string query = string.Format(@"UPDATE [dbo].[Factures]
                           SET [Libelle] = @ch1
                              ,[Title] = @ch2
                              ,[Telephone] = '{0}'
                              ,[DateFacture] = CONVERT(datetime, '{1}', 103)
                              ,[Informations] = @ch3
                              ,[Remarques] = @ch4
                         WHERE [FactureId] = {2}", model.Telephone,model.DateFacture.ToShortDateString(),
                                                  model.FactureId);
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand comm = conn.CreateCommand();
            conn.Open();
            comm.CommandText = query;
            comm.Parameters.AddWithValue("@ch1", model.Libelle);
            comm.Parameters.AddWithValue("@ch2", model.Title);
            comm.Parameters.AddWithValue("@ch3", model.Informations);
            comm.Parameters.AddWithValue("@ch4", model.Remarques);
            comm.ExecuteNonQuery();
            comm.Dispose();
            conn.Close();
        }

    }
}
